﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;

namespace MarginTrading.Services
{
    public class LoanAccountServices
    {
        private static LoanAccount GetLoanAccountFromDataReader(IDataReader dataReader)
        {
            LoanAccount retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new LoanAccount();
                retVal.ReferenceNo = dataReader["REFERENCE_NO"].ToString();
                retVal.ClientCode = dataReader["CLIENT_CODE"].ToString();
                retVal.EntryType = dataReader["ENTRY_TYPE"].ToString();
                retVal.Remarks = dataReader["REMARKS"].ToString();
                DateTime createdDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["CREATED_DATE"].ToString(), out createdDate))
                    retVal.CreatedDate = createdDate;
                double amount = 0;
                if (double.TryParse(dataReader["AMOUNT"].ToString(), out amount))
                    retVal.Amount = amount;

                //double balance = 0;
                //if (double.TryParse(dataReader["BALANCE"].ToString(), out balance))
                //    retVal.Balance = balance;
            }
            return retVal;
        }

        public static LoanAccount GetLoanAccountByReferenceNo(string referenceNo)
        {
            LoanAccount retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from loan_account where reference_no = '" + referenceNo + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetLoanAccountFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static LoanAccountCollection GetLoanAccountCollection()
        {
            LoanAccountCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from loan_account";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new LoanAccountCollection();
                    while (dataReader.Read())
                    {
                        LoanAccount item = GetLoanAccountFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetLoanAccountCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from loan_account";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static void Add(LoanAccount loanAccount)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into loan_account(REFERENCE_NO, " +
                                                      "CLIENT_CODE, "+
                                                      "ENTRY_TYPE, " +
                                                      "TRANSACTION_PARTICULARS, " +
                                                      "AMOUNT, "+
                                                      "CREATED_DATE, "+
                                                      "REMARKS) " +
                                                      "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = loanAccount.ReferenceNo;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = loanAccount.ClientCode;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = loanAccount.EntryType;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = loanAccount.TrParticulars; //Ravin1
                cmd.Parameters.Add(":v5", OracleType.Number).Value = loanAccount.Amount;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = loanAccount.CreatedDate;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = loanAccount.Remarks;//Ravin1
               
                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string referenceNo)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from loan_account where reference_no ='" + referenceNo + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(LoanAccount loanAccount)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update loan_account set " +
                                  "CLIENT_CODE = :v2, " +
                                  "ENTRY_TYPE = :v3, " +
                                  "REMARKS = :v4, " +
                                  "AMOUNT = :v5, " +
                                  //"BALANCE = :v7, " +
                                  "CREATED_DATE = :v6 where REFERENCE_NO = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = loanAccount.ReferenceNo;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = loanAccount.ClientCode;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = loanAccount.EntryType;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = loanAccount.Remarks;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = loanAccount.Amount;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = loanAccount.CreatedDate;
                //cmd.Parameters.Add(":v7", OracleType.Number).Value = loanAccount.Balance;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


        public static Dictionary<String, double> GetAccBalanceCollection(DateTime ProcessDate, String AgentCode) //Ravin
        {
            Dictionary<String, double> LoanBalance = new Dictionary<String, double>();

            
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText =   "select l.client_code, sum(l.amount) from loan_account l, client c "+
                                    "where l.created_date < :v1 and c.code = l.client_code and c.agent = :v2 " + 
                                    "group by client_code having sum(amount) > 0";

               
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = ProcessDate.AddDays(1);
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = AgentCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        if (!string.IsNullOrEmpty(dataReader[0].ToString()) &&
                                !string.IsNullOrEmpty(dataReader[1].ToString()))
                            LoanBalance.Add(dataReader[0].ToString(), double.Parse(dataReader[1].ToString()));
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return LoanBalance;
        }


        public static double GetCurrentBalance(String sClientCode)
        {
            double fRetVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select BALANCE from loan_account where CLIENT_CODE = '" + sClientCode + "' order by CREATED_DATE desc";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double fAmount = 0;
                        if (double.TryParse(dataReader["BALANCE"].ToString(), out fAmount))
                        {
                            fRetVal = fAmount;
                        }
                        else
                        {
                            fRetVal = 0;
                        }
                    }
                }

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return fRetVal;
        }

        public static DateTime GetLatestUpdateTime(String sClientCode)
        {
            DateTime retDate = DateTime.MinValue;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CREATED_DATE from loan_account where CLIENT_CODE = '" + sClientCode + "' order by CREATED_DATE desc";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        DateTime date = DateTime.MinValue;
                        if (DateTime.TryParse(dataReader["CREATED_DATE"].ToString(), out date))
                        {
                            retDate = date;
                        }
                    }
                }

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retDate;
        }


        /*
        public static LoanAccountCollection GetLoanAccCollOfDay(DateTime TrDate)
        {
            LoanAccountCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = 
                    "select * from loan_account where TO_CHAR(created_date,'mm/dd/yyyy') = "+TrDate.ToShortDateString();

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new LoanAccountCollection();
                    while (dataReader.Read())
                    {
                        LoanAccount item = GetLoanAccountFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }
        */


        public static DataTable GetLoanAccCollOfDay(DateTime TrDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText =
                    "select L.client_code, L.AMOUNT, L.TRANSACTION_PARTICULARS, C.BANK_ACC_NO from " +
                    "loan_account L, client C where TO_CHAR(created_date,'mm/dd/yyyy') = '" + TrDate.ToString("MM/dd/yyyy") + "' and l.client_code = c.code";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

    }
}
